﻿// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.ComponentModel;
using MsSqlSpatial;

public sealed partial class StoredProcedures
{
	/// <summary>
	/// Adds a geometry column to an existing table of attributes.
	/// </summary>
	/// <param name="schema_name">The name of the table schema.</param>
	/// <param name="table_name"></param>
	/// <param name="column_name"></param>
	/// <param name="srid">The srid must be an integer value reference to an entry in the SPATIAL_REF_SYS table.</param>
	/// <param name="type">The type must be an uppercase string corresponding to the geometry type, eg. ’POLYGON’ or ’MULTILINESTRING’.</param>
	[SqlProcedure]
	public static void AddGeometryColumn(
		[DefaultValue("")] SqlString schema_name,
		SqlString table_name,
		SqlString column_name,
		[DefaultValue(-1)] SqlInt32 srid,
	    [DefaultValue("GEOMETRY")] SqlString type)
	{
		string tableName = table_name.Value;
		string columnName = column_name.Value;
		int sridValue = srid.Value;
		string typeValue = type.Value;

		//1.  ensure that an entry for the SRID exists in the SPATIAL_REF_SYS table.
		//2.  add an entry to the GEOMETRY_COLUMNS table that stores the SRID for the geometry column.
		//3.  add the geometry column to the feature table using a SQL ALTER TABLE statement
		//4.  add the Spatial Reference Check Constraint to the feature table

		if (!IsValidGeometryType(typeValue))
			{
				throw new ApplicationException(
@"Invalid geometry type. Valid ones are:
\n\t" + string.Join("\n\t", validGeometryTypes));
			}

		using (SqlConnection connection = DatabaseHelper.ContextConnection)
		{
			connection.Open();
			SqlTransaction transaction = connection.BeginTransaction();
			try
			{
				using (SqlCommand command = connection.CreateCommand())
				{
					command.Transaction = transaction;

					ExecuteCreateMetaTables(command);

					if (!SpatialRefSysTable.CheckSRIDExists(command, sridValue))
					{
						throw new InvalidSridException(sridValue);
					}

					string schemaName = DatabaseHelper.CheckSchemaName(command, schema_name);

					GeometryColumnsTable.ExecuteAddMetaRecord(command, schemaName, tableName, columnName, sridValue, typeValue);
					GeometryTable.ExecuteAddColumn(command, schemaName, tableName, columnName);
					GeometryTable.ExecuteAddIndexes(command, schemaName, tableName, columnName);
					GeometryTable.ExecuteAddConstraintSRID(command, schemaName, tableName, columnName, sridValue);
					GeometryTable.ExecuteAddConstraintGeometryType(command, schemaName, tableName, columnName, typeValue);
					GeometryTable.ExecuteCreateTrigger(command, schemaName, tableName, columnName);
					ExecuteAddGeometryQueryFunctions(command, schemaName, tableName, columnName);
		
					// If you add more associated objects to the column, please 
					// verify whether they have to be added to "ImportFromDatasource" method as well.
		
					transaction.Commit();
					SqlContext.Pipe.Send(string.Format(
@"Added geometry column [{0}].[{1}].[{2}] and constraints SRID={3}, GeometryType={4}."
, schemaName, tableName, columnName, sridValue, typeValue));
				}
				
			}
			catch
			{
				transaction.Rollback();
				throw;
			}
		}
	}

	private static readonly string[] validGeometryTypes = new string[] {
		"GEOMETRY",
		"GEOMETRYCOLLECTION",
		"POINT",
		"MULTIPOINT",
		"POLYGON",
		"MULTIPOLYGON",
		"LINESTRING",
		"MULTILINESTRING"};

	private static bool IsValidGeometryType(string type)
	{
		return new List<string>(validGeometryTypes).Contains(type.ToUpperInvariant());
	}

};
